# coding=utf-8
import requests
import json
import time
import pymysql
import redis
import random
from playwright.sync_api import sync_playwright

db_host = '121.4.69.146'
db_port = 3306
conn = pymysql.connect(host=db_host,
                       user='root',
                       port=db_port,
                       password='xhhg@2020',
                       database='big_data',
                       cursorclass=pymysql.cursors.DictCursor)
redis_con = redis.Redis(host='127.0.0.1', port=6379,
                        db=2, decode_responses=True)

cur = conn.cursor()


def create_table(now_time):
    # redis创建需查询的专利号
    create_sql_payment = f"CREATE TABLE If Not Exists `by_payment_information_{now_time}` (`id` int(11) NOT NULL AUTO_INCREMENT COMMENT '自增id', `cost_category` varchar(50) DEFAULT NULL COMMENT '缴费种类', `payment_amount` varchar(50) DEFAULT NULL COMMENT '缴费金额', `pay_date` datetime DEFAULT NULL COMMENT '缴费日期', `name_payer` varchar(50) DEFAULT NULL COMMENT '缴费人', `note_the_code` varchar(50) DEFAULT NULL COMMENT '票据代码', `ticket_number` varchar(50) DEFAULT NULL COMMENT '票据号码', `create_time` datetime DEFAULT NULL COMMENT '插入时间', `updata_time` datetime DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP COMMENT '更改时间', `application_number` varchar(50) DEFAULT NULL COMMENT '申请号', PRIMARY KEY (`id`)) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;"
    cur = conn.cursor()
    cur.execute(create_sql_payment)
    conn.commit()

    create_sql_annual = f"CREATE TABLE If Not Exists `annual_fee_information_{now_time}` (`id` int(11) NOT NULL AUTO_INCREMENT, `application_number` varchar(255) DEFAULT NULL, `applicant` varchar(255) DEFAULT NULL COMMENT '专利权人', `money_type` varchar(255) DEFAULT NULL, `money` varchar(255) DEFAULT NULL, `close_date` varchar(255) DEFAULT NULL, `agency` varchar(255) DEFAULT NULL COMMENT '代理机构', `pay_status` varchar(50) DEFAULT '0', `created_at` datetime DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP, `upload_port` varchar(255) DEFAULT NULL, PRIMARY KEY (`id`) USING BTREE) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 ROW_FORMAT=COMPACT;"
    cur.execute(create_sql_annual)
    conn.commit()

    create_sql_overdue = f"CREATE TABLE If Not Exists `overdue_fine_{now_time}` (`id` int(11) NOT NULL AUTO_INCREMENT COMMENT '自增id', `application_number` varchar(50) DEFAULT NULL COMMENT '申请号', `payment_start_time` datetime DEFAULT NULL COMMENT '滞纳金开始时间', `payment_end_time` datetime DEFAULT NULL COMMENT '滞纳金结束时间', `current_annual_fee` varchar(20) DEFAULT NULL COMMENT '当前年费金额', `late_fee` varchar(20) DEFAULT NULL COMMENT '应交滞纳金额', `total` varchar(20) DEFAULT NULL COMMENT '总计', `pay_status` int(5) DEFAULT NULL COMMENT '缴费状态', `create_time` datetime DEFAULT NULL COMMENT '插入时间', `updata_time` datetime DEFAULT NULL COMMENT '更改时间', PRIMARY KEY (`id`)) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;"
    cur.execute(create_sql_overdue)
    conn.commit()


def get_patent_pay(patent_pay_res_json, application_number, inst):
    # 抓取费用信息
    code = patent_pay_res_json.get('code')
    print(f'@@{application_number}请求返回状态：{code}@@')
    patent_pay_data = patent_pay_res_json.get('data')
    # 已缴费信息
    yijiaofei_data = patent_pay_data.get('yijiaofei')
    yijiaofei_list = yijiaofei_data.get('svYijfList')
    yijiaofei_name_set = set()
    for rows in yijiaofei_list:
        yijiaofei_name_set.add(rows.get('yijiaofjfzlmc'))
        if rows:
            yijiao_sql = f'''insert into by_payment_information(cost_category, payment_amount, pay_date, name_payer, note_the_code, ticket_number, create_time, application_number)
                values('{rows.get('yijiaofjfzlmc')}', '{rows.get('yijiaofjfje')}', '{rows.get('yijiaofjfrq')}', '{rows.get('yijiaofjfrxm')}', '{rows.get('yijiaofpjdm')}', '{rows.get('yijiaofpjhm')}', NOW(), '{application_number}')'''
            try:
                cur = conn.cursor()
                cur.execute(yijiao_sql)
                conn.commit()
                print(yijiao_sql)
            except:
                time.sleep(5)
                cur = conn.cursor()
                cur.execute(yijiao_sql)
                conn.commit()
    print('##application_number:%s--已缴费信息入库完成##' % application_number)

    # 应缴费信息
    yinjiaofei_data = patent_pay_data.get('yingjiaofei')
    yinjiaofei_list = yinjiaofei_data.get('svYingjfList')
    for rows in yinjiaofei_list:
        if rows:
            if rows.get('yingjiaoffyzlmc') not in yijiaofei_name_set:
                yinjiao_sql = f'''insert into annual_fee_information (application_number, money_type, money, close_date, pay_status, created_at)
              values('{application_number}', '{rows.get('yingjiaoffyzlmc')}', '{rows.get('yingjiaoje')}', '{rows.get('jiaofeijzr')}', '{rows.get('yingjiaoffyzt')}', NOW())'''
                try:
                    cur = conn.cursor()
                    cur.execute(yinjiao_sql)
                    conn.commit()
                    print(yinjiao_sql)
                except:
                    time.sleep(5)
                    cur = conn.cursor()
                    cur.execute(yinjiao_sql)
                    conn.commit()
                
    print('@@application_number:%s--应缴费信息入库完成@@' % application_number)

    # 滞纳费信息
    zhinafei_data = patent_pay_data.get('zhinajin')
    zhinafei_list = zhinafei_data.get('svZnjList')
    for rows in zhinafei_list:
        if rows:
            date_str = rows.get('zhinajjfsj')
            date_list = date_str.split('到')
            date_list = [item.replace(
                '年', '-').replace('月', '-').replace('日', '') for item in date_list]
            start_date = date_list[0]
            end_date = date_list[1]
            zhinajin_sql = f'''insert into overdue_fine(application_number, payment_start_time, payment_end_time, current_annual_fee, late_fee, total, create_time, updata_time) values('{application_number}','{start_date}','{end_date}','{rows.get('zhinajdqnfje')}','{rows.get('zhinajyjznje')}','{rows.get('zhinajzj')}',NOW(),NOW())'''
            try:
                cur = conn.cursor()
                cur.execute(zhinajin_sql)
                conn.commit()
            except:
                time.sleep(5)
                cur = conn.cursor()
                cur.execute(zhinajin_sql)
                conn.commit()
            print(zhinajin_sql)
    print('**application_number:%s--已缴费信息入库完成**' % application_number)

    if inst == 'XHHG' and yinjiaofei_list == [] and yijiaofei_list == [] and zhinafei_list == []:
        try:
            redis_con.sadd("tmp_协和端口查询", application_number)
        except:
            time.sleep(5)
            redis_con.sadd("tmp_协和端口查询", application_number)

        print('@@XHHG未查到相关信息，使用XH进行查询@@')
    elif inst == 'XH' and yinjiaofei_list == [] and yijiaofei_list == [] and zhinafei_list == []:
        try:
            redis_con.sadd("tmp_外部接口查询", application_number)
        except:
            time.sleep(5)
            redis_con.sadd("tmp_外部接口查询", application_number)
        print('@@XH端口未查到相关信息，使用外部接口查询@@')
    elif inst == 'WB' and yinjiaofei_list == [] and yijiaofei_list == [] and zhinafei_list == []:
        try:
            redis_con.sadd("tmp_全部接口未查到", application_number)
        except:
            redis_con.sadd("tmp_全部接口未查到", application_number)
        print('@@全部接口均未查到@@')


def run(i, inst, port):
    all_start_time = time.time()
    with sync_playwright() as p:
        storage_state_path = f'./auth/{inst}_state.json'
        with open(storage_state_path) as file:
            storage_state = json.load(file)
            origins = storage_state.get('origins')
            local_stroge_list = [item.get('localStorage') for item in origins]
            for local_stroge in local_stroge_list:
                for item in local_stroge:
                    if item.get('name') == 'ACCESS_TOKEN':
                        authorization = item.get('value')
        print(f'成功读取{inst}登录信息:{authorization}')
        browser = p.firefox.launch()
        context = browser.new_context(
            base_url='https://cpquery.cponline.cnipa.gov.cn', storage_state=storage_state_path)
        context.set_default_timeout(600000)
        context.set_default_navigation_timeout(600000)
        page = context.new_page()
        page.goto(
            'https://cpquery.cponline.cnipa.gov.cn/chinesepatent/index')
        page.wait_for_load_state('networkidle')
        scraped_nums = 0
        while redis_con.scard(port):
            sqh = redis_con.spop(port).replace("ZL", '')
            start_time = time.time()
            print(f'@@开始查询申请号:{sqh}_专利费用信息@@')
            api_request_context = context.request
            headers = {
                "Accept": "application/json, text/plain, */*",
                "Content-Type": "application/json;charset=utf-8",
                "Authorization": 'Bearer ' + authorization,
                "userType": "USER_RZ_DAILIJIG"
            }
            fyxx_response = api_request_context.post(
                '/api/view/gn/fyxx', data={'zhuanlisqh': sqh}, headers=headers, timeout=0)
            patent_pay_res_json = fyxx_response.json()
            get_patent_pay(patent_pay_res_json, sqh, inst)
            end_time = time.time()
            print(f"{sqh}_本次耗时: {end_time-start_time}")
            need_time = round((end_time - start_time) *
                              redis_con.scard(port) / 60, 1)
            scraped_nums += 1
            print(
                f"进程{i}已抓取专利数：{scraped_nums}，剩余{redis_con.scard(port)}个，大约还需要{need_time}分钟")
            page.reload()
    all_end_time = time.time()
    print(f"@@总耗时：{all_end_time-all_start_time}@@")


if __name__ == '__main__':
    # now_time='0523'
    # create_table(now_time=now_time)
    # print('@@成功创建目标表@@')
    port_dict = {'XHHG': 'tmp_信合红谷接口查询',
                 'XH': 'tmp_协和端口查询', 'WB': "tmp_外部接口查询"}
    inst = 'XHHG'
    port = 'tmp_临时费用'
    # from multiprocessing.pool import Pool
    # p = Pool(6)
    # for i in range(6):
    #     p.apply_async(run, args=(i,inst, port))
    # p.close()
    # p.join()
    run(0, inst, port)
    conn.close()
